﻿namespace Journey.Data.AdoNet
{
    using System;
    using System.Data;
    using System.Data.Common;
    using System.Linq;
    using System.Threading.Tasks;

    /// <summary>
    /// 数据提供程序基类。
    /// </summary>
    public abstract partial class DataProviderBase
    {
        /// <summary>
        /// 数据库连接字符串。
        /// </summary>
        protected string ConnectionString
        {
            get
            {
                if (string.IsNullOrWhiteSpace(_connectionString))
                {
                    _connectionString = DataConfigurationManager.Current.GetDefaultConnection().ConnectionString;
                }

                return _connectionString;
            }
            set
            {
                _connectionString = value;
            }
        }

        /// <summary>
        /// 查询数据。
        /// </summary>
        /// <param name="commandType">命令类型。</param>
        /// <param name="sql">SQL 语句或者存储过程名称。</param>
        /// <param name="commandParameters">SQL 参数。</param>
        /// <returns>数据读取器。</returns>
        public async Task<DbDataReader> SqlQueryAsync(CommandType commandType, string sql, params IDataParameter[] commandParameters)
        {
            return await ExecuteReaderAsync(ConnectionString, commandType, sql, commandParameters);
        }

        /// <summary>
        /// 查询单条数据。
        /// </summary>
        /// <param name="commandType">命令类型。</param>
        /// <param name="sql">SQL 语句或者存储过程名称。</param>
        /// <param name="commandParameters">SQL 参数。</param>
        /// <returns>查询结果。</returns>
        public async Task<object> SingleAsync(CommandType commandType, string sql, params IDataParameter[] commandParameters)
        {
            // 创建并打开数据库连接对象,操作完成释放对象.
            using (var connection = GetDbProviderFactory().CreateConnection())
            {
                if (connection == null) throw new Exception("connection 对象不可以是空的。");

                connection.ConnectionString = ConnectionString;
                connection.Open();

                // 调用指定数据库连接字符串重载方法.
                return await ExecuteScalarAsync(connection, null, commandType, sql, commandParameters);
            }
        }

        /// <summary>
        /// 执行 SQL 命令。
        /// </summary>
        /// <param name="commandType">命令类型。</param>
        /// <param name="sql">SQL 语句或者存储过程名称。</param>
        /// <param name="commandParameters">SQL 参数。</param>
        /// <exception cref="Exception"></exception>
        /// <returns>影响行数。</returns>
        public async Task<int> ExecuteSqlCommandAsync(CommandType commandType, string sql, params IDataParameter[] commandParameters)
        {
            using (var connection = GetDbProviderFactory().CreateConnection())
            {
                if (connection == null) throw new Exception("connection 对象不可以是空的。");

                connection.ConnectionString = ConnectionString;
                connection.Open();

                return await ExecuteNonQueryAsync(connection, null, commandType, sql, commandParameters);
            }
        }

        #region ExecuteNonQuery

        /// <summary>
        /// 执行指定数据库连接对象的命令
        /// </summary>
        /// <remarks>
        /// 示例:
        ///  int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new IDataParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">一个有效的数据库连接对象</param>
        /// <param name="transaction">一个有效的事务,或者为 'null'</param>
        /// <param name="commandType">命令类型(存储过程,命令文本或其它.)</param>
        /// <param name="commandText">T存储过程名称或SQL语句</param>
        /// <param name="commandParameters">SqlParamter参数数组</param>
        /// <returns>返回影响的行数</returns>
        protected virtual async Task<int> ExecuteNonQueryAsync(
            DbConnection connection, DbTransaction transaction, CommandType commandType, string commandText, params IDataParameter[] commandParameters)
        {
            if (connection == null)
                throw new ArgumentNullException("connection");

            // 创建DbCommand命令,并进行预处理
            var command = GetDbProviderFactory().CreateCommand();
            if (command == null) throw new Exception("command");
            command.CommandTimeout = 600;
            bool mustCloseConnection;
            PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);

            // Finally, execute the command
            var retval = await command.ExecuteNonQueryAsync();

            // 清除参数,以便再次使用.
            command.Parameters.Clear();

            return retval;
        }

        #endregion ExecuteNonQuery

        #region ExecuteScalar

        /// <summary>
        /// 执行指定数据库连接对象的命令,指定参数,返回结果集中的第一行第一列.
        /// </summary>
        /// <remarks>
        /// 示例:
        ///  int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new IDataParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connection">一个有效的数据库连接对象</param>
        /// <param name="transaction">一个有效的事务,或者为 'null'</param>
        /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="commandText">存储过程名称或SQL语句</param>
        /// <param name="commandParameters">分配给命令的SqlParamter参数数组</param>
        /// <returns>返回结果集中的第一行第一列</returns>
        protected virtual async Task<object> ExecuteScalarAsync(DbConnection connection, DbTransaction transaction, CommandType commandType, string commandText, params IDataParameter[] commandParameters)
        {
            if (connection == null)
                throw new ArgumentNullException("connection");

            object retval = null;

            try
            {
                // 创建DbCommand命令,并进行预处理
                var command = GetDbProviderFactory().CreateCommand();

                bool mustCloseConnection;
                PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);

                // 执行DbCommand命令,并返回结果.
                if (command != null)
                {
                    retval = await command.ExecuteScalarAsync();

                    // 清除参数,以便再次使用.
                    command.Parameters.Clear();
                }

                if (mustCloseConnection)
                    connection.Close();
            }
            catch
            {
                connection.Close();
                throw;
            }

            return retval;
        }

        #endregion ExecuteScalar

        #region ExecuteReader

        /// <summary>
        /// 执行指定数据库连接对象的数据阅读器.
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="commandText">存储过程名或 SQL 语句</param>
        /// <param name="commandParameters">IDataParameters 参数数组, 如果没有参数则为 'null'</param>
        /// <returns>返回包含结果集的 IDataReader</returns>
        protected virtual async Task<DbDataReader> ExecuteReaderAsync(string connectionString, CommandType commandType, string commandText, params IDataParameter[] commandParameters)
        {
            if (string.IsNullOrEmpty(connectionString))
                throw new ArgumentNullException("connectionString");

            DbConnection connection = null;

            try
            {
                connection = GetDbProviderFactory().CreateConnection();

                if (connection == null) throw new Exception("connection");

                connection.ConnectionString = connectionString;
                connection.Open();

                return await ExecuteReaderAsync(connection, null, commandType, commandText, commandParameters, DBConnectionOwnership.Internal);
            }
            catch
            {
                // If we fail to return the SqlDatReader, we need to close the connection ourselves
                if (connection != null)
                {
                    connection.Close();
                }
                throw;
            }
        }

        /// <summary>
        /// 执行指定数据库连接对象的数据阅读器.
        /// </summary>
        /// <remarks>
        /// 如果是BaseDbHelper打开连接,当连接关闭DataReader也将关闭.
        /// 如果是调用都打开连接,DataReader由调用都管理.
        /// </remarks>
        /// <param name="connection">一个有效的数据库连接对象</param>
        /// <param name="transaction">一个有效的事务,或者为 'null'</param>
        /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="commandText">存储过程名或 SQL 语句</param>
        /// <param name="commandParameters">IDataParameters参数数组,如果没有参数则为'null'</param>
        /// <param name="connectionOwnership">标识数据库连接对象是由调用者提供还是由BaseDbHelper提供</param>
        /// <returns>返回包含结果集的DbDataReader</returns>
        protected virtual async Task<DbDataReader> ExecuteReaderAsync(DbConnection connection, DbTransaction transaction, CommandType commandType, string commandText, IDataParameter[] commandParameters, DBConnectionOwnership connectionOwnership)
        {
            if (connection == null)
                throw new ArgumentNullException("connection");

            bool mustCloseConnection = false;
            // 创建命令
            var command = GetDbProviderFactory().CreateCommand();

            if (command == null) throw new Exception("command");

            command.CommandTimeout = 180;
            try
            {
                PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);

                // 创建数据阅读器
                DbDataReader dataReader;

                if (connectionOwnership == DBConnectionOwnership.External)
                {
                    dataReader = await command.ExecuteReaderAsync();
                }
                else
                {
                    dataReader = await command.ExecuteReaderAsync(CommandBehavior.CloseConnection);
                }

                if (command.Parameters.Cast<IDataParameter>().All(commandParameter => commandParameter.Direction == ParameterDirection.Input))
                {
                    //cmd.Dispose();
                    command.Parameters.Clear();
                }

                return dataReader;
            }
            catch
            {
                if (mustCloseConnection)
                    connection.Close();
                throw;
            }
        }

        #endregion ExecuteReader

        /// <summary>
        /// 枚举,标识数据库连接是由BaseDbHelper提供还是由调用者提供。
        /// </summary>
        protected enum DBConnectionOwnership
        {
            /// <summary>
            /// 由BaseDbHelper提供连接。
            /// </summary>
            Internal,

            /// <summary>
            /// 由调用者提供连接。
            /// </summary>
            External
        }
    }
}